﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Xml.Linq;
using SqlDao;

namespace clf.code.first
{
    internal class MysqlHandle : BaseHadnle, IHandller
    {
        //Server=192.168.88.3;Database=weightsys;User Id=admin;Password=yourPwd;pooling=false;CharSet=utf8;port=33069
        private string Server;
        private string DataBase;
        private string UserId;
        private string Password;
        private int Port;
        #region 
        public MysqlHandle(string server, string dataBase, string userId, string password, int port = 3306)
        {
            this.Server = server;
            this.DataBase = dataBase;
            this.UserId = userId;
            this.Password = password;
            this.Port = port;
            this.databaseName = dataBase;

            this.connectStr = MysqlConnectionStringBuilder.GetConnectionString(this.Server, this.DataBase, this.UserId, this.Password, this.Port, true);

            if (instance == null)
            {
                instance = new MySqlHelper(this.connectStr);
            }
#if DEBUG
            DbHelper.isDebug = true;
            DbHelper.enableErrorLog = true;
#endif


        }
        #endregion

        public List<string> GetAllTables()
        {
            return instance.GetAllTableName(this.databaseName).ToList();
        }

        //1.新加入的类，增加表
        //2.后加入的类，更新表
        //数据表中多余Table， 不处理

        //1.新加入的类Field，增加Column
        //2.后加入的类Field，更新表 Column
        //3.数据表中多余Colum 删除


        public void StartHandle(Assembly assembly, Action<ProgressResult> callback)
        {
            //CancellationTokenSource cts = new CancellationTokenSource();
            var thread = new Thread((obj) =>
            {
                string assemblyName = assembly.FullName;
                int progress = 0;
                Notify(callback, progress, "mysql start handle:" + assemblyName);
                List<Type> classTypes = null;

                try
                {
                    classTypes = assembly.GetTypes().Where((t) =>
                    {
                        return t.GetCustomAttribute<TableAttribute>() != null;
                    }).ToList();
                }
                catch (Exception ex)
                {
                    if (ex is ReflectionTypeLoadException exception)
                    {
                        var ts = exception.Types.ToList();
                        classTypes = ts.Where((t) =>
                        {
                            return t.ToString() != "null" && t.GetCustomAttribute<TableAttribute>() != null;
                        }).ToList();
                    }
                    if (classTypes == null)
                        throw ex;
                }

                if (classTypes == null)
                {
                    Notify(callback, 0, "未能获取到 clf.code.first 注解的实体类");
                    return;
                }

                //处理数据库存，如何不存在添加
                HandleDatabases(callback);

                //记录表中现有的列
                List<string> names = new List<string>();
                mTables = GetAllTable();

                //新加入的类
                List<Type> newTypes = new List<Type>();
                List<Type> oldTypes = new List<Type>();
                Notify(callback, 15, "正在进行分类");

                for (int i = 0; i < classTypes.Count; i++)
                {
                    var t = classTypes[i];
                    var exist = IsTypeInTables(t);
                    if (exist)
                    {
                        oldTypes.Add(t);
                    }
                    else
                    {
                        newTypes.Add(t);
                    }
                }
                Notify(callback, 17, "分类完成");

                int total = newTypes.Count + oldTypes.Count + 10;
                StringBuilder createSqlBuilder = new StringBuilder();

                #region 创建表

                for (int i = 0; i < newTypes.Count; i++)
                {
                    var nt = newTypes[i];
                    Notify(callback, ((progress + i) / total) * 100, "handle :" + nt.Name);

                    var tableAttr = nt.GetCustomAttribute<TableAttribute>();

                    if (tableAttr != null)
                    {
                        string tableName = string.IsNullOrEmpty(tableAttr.Name) ? Helper.CamelCaseToDBnameing(nt.Name) : tableAttr.Name;
                        string pk = "PRIMARY KEY (`id`)";
                        //1.add table
                        createSqlBuilder.AppendLine($" -- `{tableName}`  ");
                        createSqlBuilder.AppendLine($"CREATE TABLE `{tableName}` (");
                        //2.add columns
                        var fields = nt.GetFields()?.Where((f) =>
                        {
                            return f.GetCustomAttribute<ColumnsAttribute>() != null || f.GetCustomAttribute<IdAttribute>() != null;
                        }).ToList();

                        int ids = 0;

                        SortByName(fields);

                        foreach (FieldInfo fi in fields)
                        {
                            string res = "";
                            var colAttr = fi.GetCustomAttribute<ColumnsAttribute>();
                            var idAttr = fi.GetCustomAttribute<IdAttribute>();
                            if (colAttr != null || idAttr != null)
                            {
                                string colomnName = "";
                                if (idAttr != null)
                                {
                                    ids++;
                                    if (ids > 1) { throw new Exception("IdAttribute不能同时使用多个"); }
                                    colomnName = Helper.CamelCaseToDBnameing(fi.Name);
                                    pk = $"PRIMARY KEY (`{colomnName}`)";
                                }
                                if (colAttr != null)
                                {
                                    colomnName = string.IsNullOrEmpty(colAttr.Name) ? Helper.CamelCaseToDBnameing(fi.Name) : colAttr.Name;
                                    names.Add(colomnName);
                                    res += $"`{colomnName}` ";
                                    //type
                                    res += GetTypeStr(colAttr.DbType);

                                    //len
                                    res += GetLenthStr(colAttr.DbType, colAttr.Length, colAttr.Digits);

                                    // null
                                    if (colAttr.IsNull == false)
                                    {
                                        res += $" NOT NULL  ";
                                    }
                                    //default
                                    if (idAttr == null)
                                    {
                                        if (colAttr.DbType != MySqlDbType.Text && colAttr.DbType != MySqlDbType.LongText)
                                        {
                                            res += GetDefaultStr(colAttr.DbType, colAttr.DefaultValue);
                                        }
                                    }
                                    if (!string.IsNullOrEmpty(colAttr.Comment))
                                    {
                                        res += $" COMMENT '{colAttr.Comment}'";
                                    }
                                    if (idAttr != null && idAttr.AutoIncrement == true)
                                    {
                                        res += " AUTO_INCREMENT ";
                                    }
                                    res += " ,";
                                }
                                else
                                {
                                    res += $"`{colomnName}` ";
                                }

                            }
                            //Append colouns
                            createSqlBuilder.AppendLine(res);
                        }

                        //3.PRIMARY KEY
                        createSqlBuilder.AppendLine(pk);
                        //4.end table
                        createSqlBuilder.AppendLine($") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_cs_0900_ai_ci COMMENT='{tableAttr.Comment}';");
                        createSqlBuilder.AppendLine($"-- end `{tableName}`");
                    }
                }
                createSqlBuilder.AppendLine();
                #endregion

                #region 更新字段
                for (int i = 0; i < oldTypes.Count; i++)
                {
                    var ot = oldTypes[i];
                    Notify(callback, ((progress + i) / total) * 100, "handle :" + ot.Name);
                    var tableAttri = ot.GetCustomAttribute<TableAttribute>();
                    if (tableAttri != null)
                    {
                        string tableName = Helper.CamelCaseToDBnameing(ot.Name);
                        if (!string.IsNullOrEmpty(tableAttri.Name))
                        {
                            tableName = tableAttri.Name;
                        }

                        var fields = ot.GetFields().Where((f) =>
                        {
                            //主键 只会第一次构建时创建，一般是（id），后期修改主键无效。
                            // return f.GetCustomAttribute<IdAttribute>() != null ||
                            return f.GetCustomAttribute<ColumnsAttribute>() != null;
                        }).ToList();
                        createSqlBuilder.AppendLine();
                        foreach (var field in fields)
                        {
                            string res = "";
                            var colAttr = field.GetCustomAttribute<ColumnsAttribute>();
                            if (colAttr != null)
                            {
                                string cname = string.IsNullOrEmpty(colAttr.Name) ? Helper.CamelCaseToDBnameing(field.Name) : colAttr.Name;
                                names.Add(cname);
                                //type
                                string oldtype = GetTypeStr(colAttr.DbType);
                                res += oldtype;

                                string len = GetLenthStr(colAttr.DbType, colAttr.Length, colAttr.Digits);
                                res += len;
                                oldtype += len;
                                // null
                                if (colAttr.IsNull == false)
                                {
                                    res += $" NOT NULL  ";
                                }
                                //default
                                if (field.GetCustomAttribute<IdAttribute>() == null)
                                {
                                    if (colAttr.DbType != MySqlDbType.Text && colAttr.DbType != MySqlDbType.LongText)
                                    {
                                        res += GetDefaultStr(colAttr.DbType, colAttr.DefaultValue);
                                    }
                                }

                                if (!string.IsNullOrEmpty(colAttr.Comment))
                                {
                                    res += $" COMMENT '{colAttr.Comment}'";
                                }
                                var tableColumns = instance.GetTableColumnSchema<MysqlColumnSchema>(databaseName, tableName);

                                var column = tableColumns.Find((c) => { return c.ColumnName.Equals(cname); });

                                if (column != null)
                                {
                                    // id 不可以修改
                                    if (oldtype.Trim() != column.Type && cname != "id")
                                    {
                                        //  exist and type diff, modify
                                        createSqlBuilder.AppendLine($"ALTER TABLE {tableName} MODIFY COLUMN {cname} {res} ;");
                                    }
                                }
                                else
                                {   // don't exist 
                                    createSqlBuilder.AppendLine($"ALTER TABLE {tableName} ADD COLUMN {cname} {res} ;");
                                }
                                createSqlBuilder.AppendLine();
                            }
                        }
                    }
                }

                #endregion

                #region 删除多余的列
                createSqlBuilder.AppendLine("--  删除多余的列");
                foreach (var item in oldTypes)
                {
                    Notify(callback, 97, "mysql end handle:删除" + item.Name + "多余的列");
                    string tableName = Helper.CamelCaseToDBnameing(item.Name);
                    List<MysqlColumnSchema> columnList = instance.GetTableColumnSchema<MysqlColumnSchema>(databaseName, tableName);

                    FieldInfo[] fieldInfos = item.GetFields();
                    var notCs = columnList.Where((i) => { return !fieldInfos.Any((f) => { return Helper.CamelCaseToDBnameing(f.Name).Equals(i.ColumnName); }); }).ToList();

                    foreach (var notItes in notCs)
                    {
                        createSqlBuilder.AppendLine($"ALTER TABLE {tableName} DROP COLUMN `{notItes.ColumnName}`;");
                    }
                }
                createSqlBuilder.AppendLine("--  end file");
                #endregion


                //saveFile(createSqlBuilder.ToString());

                string sql = RemoveEmptyLines(createSqlBuilder).ToString();

                if (string.IsNullOrEmpty(sql) || sql.Length <= 0)
                {
                    Notify(callback, 100, "mysql end handle Success: nothing to do .  "); ;
                }
                else
                {
                    int rows = Excute(createSqlBuilder.ToString());

                    Notify(callback, 100, "mysql end handle: total " + rows);
                }

            });
            thread.Start();
        }


        private void HandleDatabases(Action<ProgressResult> callback)
        {
            var noDatabaseConnstring = MysqlConnectionStringBuilder.GetConnectionString(this.Server, "", UserId, Password, Port);
            var TempHelper =   new MySqlHelper(noDatabaseConnstring);
            if(TempHelper != null)
            {
                var isExist  = TempHelper.ExistDatabase(this.DataBase);
                if(isExist == false)
                {
                   var res = TempHelper.CreateDatabase(this.DataBase);
                    Notify(callback, 10, res ? $"创建{this.DataBase} 成功" : "创建{this.DataBase} 失败");
                }
                else
                {
                    Notify(callback, 10, $"{this.DataBase} 已经存在");
                }
            }
            noDatabaseConnstring = null;
            TempHelper = null;
        }


        //获取所有表
        public List<DbSchema> GetAllTable()
        {
            var list = instance.GetAllTableSchema<DbSchema>(this.databaseName);
            return list;
        }

        private void SaveFile(string conent)
        {
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sql");
            string filename = Path.Combine(path, "build.sql");
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);
            if (!File.Exists(filename))
            {
                var fs = File.Create(filename);
                fs.Close();
            }
            File.WriteAllText(filename, conent);
        }



        private string GetDefaultStr(MySqlDbType type, object DefaultValue)
        {
            string res = "";
            switch (type)
            {
                case MySqlDbType.VarChar:
                case MySqlDbType.VarString:
                case MySqlDbType.Text:
                case MySqlDbType.String:
                case MySqlDbType.LongText:
                    res += $" DEFAULT '{DefaultValue}' ";
                    break;
                case MySqlDbType.Int16:
                case MySqlDbType.Int24:
                case MySqlDbType.Int32:
                case MySqlDbType.Int64:
                    if (DefaultValue == null)
                    {
                        res = "";
                    }
                    else
                    {
                        res += $" DEFAULT {DefaultValue} ";
                    }
                    break;
                default:
                    if (DefaultValue == null)
                    {
                        res += $" DEFAULT NULL ";
                    }
                    else
                    {
                        res += $" DEFAULT {DefaultValue} ";
                    }
                    break;

            }
            return res;
        }

        private string GetLenthStr(MySqlDbType type, int len, int digits)
        {
            string res = "";
            if (len > 0)
            {
                switch (type)
                {
                    case MySqlDbType.Float:
                    case MySqlDbType.Double:
                    case MySqlDbType.Decimal:
                        res += $"({len},{digits}) ";
                        break;
                    case MySqlDbType.DateTime:
                    case MySqlDbType.Date:
                    case MySqlDbType.Time:
                        res += "";
                        break;
                    case MySqlDbType.VarChar:
                    case MySqlDbType.VarString:
                    case MySqlDbType.String:
                    case MySqlDbType.Text:
                        res += $"({len}) ";
                        break;
                    default:

                        break;
                }
            }
            return res;
        }

        private string GetTypeStr(MySqlDbType type)
        {
            string res = "";
            switch (type)
            {
                case MySqlDbType.Int16:
                    res += $" tinyint";
                    break;
                case MySqlDbType.Int24:
                case MySqlDbType.Int32:
                    res += $" int";
                    break;
                case MySqlDbType.Int64:
                    res += $" bigint";
                    break;
                case MySqlDbType.Float:
                case MySqlDbType.Double:
                    res += $" double";
                    break;
                case MySqlDbType.Decimal:
                    res += $" decimal";
                    break;
                case MySqlDbType.VarChar:
                    res += $" varchar";
                    break;
                case MySqlDbType.String:
                    res += $" varchar";
                    break;
                case MySqlDbType.DateTime:
                    res += $" datetime";
                    break;
                case MySqlDbType.Text:
                    res += $" text";
                    break;
                default:
                    string t = Helper.CamelCaseToDBnameing(type.ToString()).Replace("_", "");
                    res += $" {type}";
                    break;
            }
            return res;
        }

    }
}
